package org.example.service;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * Excel去重服务
 * 功能：导入Excel文件，按手机号去重，导出到本地文件夹
 */
@Slf4j
@Service
public class ExcelDeduplicationService {
//output/excel_dedup/
    private static final String OUTPUT_DIR = "C:\\AAAAmessage\\";
    private static final DateTimeFormatter TIMESTAMP_FORMAT = DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss");

    /**
     * 处理Excel文件去重
     * @param file 上传的Excel文件
     * @return 处理结果信息
     */
    public Map<String, Object> processExcelDeduplication(MultipartFile file) {
        Map<String, Object> result = new HashMap<>();
        long startTime = System.currentTimeMillis();

        try {
            // 1. 验证文件
            validateFile(file);

            // 2. 读取Excel数据
            List<Map<String, Object>> originalData = readExcelData(file);
            log.info("原始数据行数: {}", originalData.size());

            // 3. 按手机号去重（保留第一条）
            List<Map<String, Object>> deduplicatedData = deduplicateByPhone(originalData);
            log.info("去重后数据行数: {}", deduplicatedData.size());

            // 4. 生成输出文件路径
            String outputFileName = generateOutputFileName(file.getOriginalFilename());
            String outputPath = OUTPUT_DIR + outputFileName;

            // 5. 创建输出目录
            createOutputDirectory();

            // 6. 导出去重后的数据
            exportToExcel(deduplicatedData, outputPath);

            // 7. 计算处理结果
            long endTime = System.currentTimeMillis();
            long duration = endTime - startTime;

            result.put("success", true);
            result.put("originalCount", originalData.size());
            result.put("deduplicatedCount", deduplicatedData.size());
            result.put("removedCount", originalData.size() - deduplicatedData.size());
            result.put("outputPath", outputPath);
            result.put("outputFileName", outputFileName);
            result.put("duration", duration);
            result.put("message", String.format("去重完成！原始%d条，去重后%d条，删除%d条重复数据", 
                originalData.size(), deduplicatedData.size(), originalData.size() - deduplicatedData.size()));

            log.info("Excel去重处理完成: {}", result.get("message"));

        } catch (Exception e) {
            log.error("Excel去重处理失败", e);
            result.put("success", false);
            result.put("error", e.getMessage());
        }

        return result;
    }

    /**
     * 验证上传文件
     */
    private void validateFile(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new IllegalArgumentException("文件不能为空");
        }

        String fileName = file.getOriginalFilename();
        if (fileName == null || (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls"))) {
            throw new IllegalArgumentException("只支持Excel文件格式(.xlsx, .xls)");
        }

        if (file.getSize() > 50 * 1024 * 1024) { // 50MB限制
            throw new IllegalArgumentException("文件大小不能超过50MB");
        }
    }

    /**
     * 读取Excel数据（完全保持原始格式和列顺序）
     */
    private List<Map<String, Object>> readExcelData(MultipartFile file) throws IOException {
        List<Map<String, Object>> dataList = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表

            // 读取表头（保持原始顺序）
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new IllegalArgumentException("Excel文件没有表头行");
            }

            List<String> headers = new ArrayList<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                String header = cell != null ? getCellValueAsString(cell) : "Column" + (i + 1);
                headers.add(header);
            }

            log.info("Excel表头: {}", headers);

            // 读取数据行（保持原始顺序）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                // 使用LinkedHashMap保持列的顺序
                Map<String, Object> rowData = new LinkedHashMap<>();
                boolean hasData = false;

                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    String value = cell != null ? getCellValueAsString(cell) : "";
                    rowData.put(headers.get(j), value);
                    if (!value.trim().isEmpty()) {
                        hasData = true;
                    }
                }

                // 只添加有数据的行
                if (hasData) {
                    dataList.add(rowData);
                }
            }
        }

        return dataList;
    }

    /**
     * 获取单元格值作为字符串
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 处理数字，避免科学计数法
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == (long) numericValue) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 按手机号去重（保留第一条，完全保持原始格式）
     */
    private List<Map<String, Object>> deduplicateByPhone(List<Map<String, Object>> originalData) {
        Map<String, Map<String, Object>> phoneMap = new LinkedHashMap<>(); // 使用LinkedHashMap保持顺序
        List<Map<String, Object>> deduplicatedData = new ArrayList<>();

        for (Map<String, Object> row : originalData) {
            // 尝试不同的手机号字段名
            String phone = getPhoneNumber(row);
            
            if (phone != null && !phone.trim().isEmpty()) {
                // 如果手机号不存在，则添加（保留第一条）
                if (!phoneMap.containsKey(phone)) {
                    phoneMap.put(phone, row);
                    deduplicatedData.add(row);
                } else {
                    log.debug("发现重复手机号: {}, 跳过该行", phone);
                }
            } else {
                // 没有手机号的行也保留
                deduplicatedData.add(row);
            }
        }

        return deduplicatedData;
    }

    /**
     * 获取手机号（尝试多个可能的字段名）
     */
    private String getPhoneNumber(Map<String, Object> row) {
        // 可能的手机号字段名
        String[] phoneFields = {"手机号", "phone", "mobile", "手机", "联系电话", "tel"};
        
        for (String field : phoneFields) {
            Object value = row.get(field);
            if (value != null) {
                String phone = value.toString().trim();
                // 简单验证手机号格式（11位数字）
                if (phone.matches("^1[3-9]\\d{9}$")) {
                    return phone;
                }
            }
        }
        
        return null;
    }

    /**
     * 生成输出文件名
     */
    private String generateOutputFileName(String originalFileName) {
        String timestamp = LocalDateTime.now().format(TIMESTAMP_FORMAT);
        String baseName = originalFileName != null ? 
            originalFileName.substring(0, originalFileName.lastIndexOf('.')) : "deduplicated";
        return baseName + "_去重_" + timestamp + ".xlsx";
    }

    /**
     * 创建输出目录
     */
    private void createOutputDirectory() {
        File outputDir = new File(OUTPUT_DIR);
        if (!outputDir.exists()) {
            boolean created = outputDir.mkdirs();
            if (created) {
                log.info("创建输出目录: {}", OUTPUT_DIR);
            } else {
                log.warn("创建输出目录失败: {}", OUTPUT_DIR);
            }
        }
    }

    /**
     * 导出数据到Excel（完全保持原始格式和列顺序）
     */
    private void exportToExcel(List<Map<String, Object>> data, String outputPath) throws IOException {
        if (data.isEmpty()) {
            throw new IllegalArgumentException("没有数据可导出");
        }

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("去重数据");

            // 创建样式（保持原始格式，不添加额外样式）
            CellStyle dataStyle = createSimpleDataStyle(workbook);

            // 获取表头（保持原始顺序）
            Map<String, Object> firstRow = data.get(0);
            List<String> headerList = new ArrayList<>(firstRow.keySet());

            // 创建表头行（保持原始表头格式）
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headerList.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headerList.get(i));
                // 不设置特殊样式，保持原始格式
            }

            // 创建数据行（完全保持原始数据格式）
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Map<String, Object> rowData = data.get(i);

                for (int j = 0; j < headerList.size(); j++) {
                    Cell cell = row.createCell(j);
                    Object value = rowData.get(headerList.get(j));
                    setCellValue(cell, value);
                    // 不设置特殊样式，保持原始格式
                }
            }

            // 不自动调整列宽，保持原始列宽
            // 写入文件
            try (FileOutputStream fileOut = new FileOutputStream(outputPath)) {
                workbook.write(fileOut);
            }
        }

        log.info("数据导出完成: {}", outputPath);
    }

    /**
     * 创建简单数据样式（保持原始格式）
     */
    private CellStyle createSimpleDataStyle(Workbook workbook) {
        // 返回默认样式，不添加任何格式化
        return workbook.createCellStyle();
    }

    /**
     * 设置单元格值
     */
    private void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 获取输出目录下的文件列表
     */
    public List<Map<String, Object>> getOutputFiles() {
        List<Map<String, Object>> fileList = new ArrayList<>();
        File outputDir = new File(OUTPUT_DIR);
        
        if (outputDir.exists() && outputDir.isDirectory()) {
            File[] files = outputDir.listFiles((dir, name) -> name.endsWith(".xlsx"));
            if (files != null) {
                for (File file : files) {
                    Map<String, Object> fileInfo = new HashMap<>();
                    fileInfo.put("fileName", file.getName());
                    fileInfo.put("filePath", file.getAbsolutePath());
                    fileInfo.put("fileSize", file.length());
                    fileInfo.put("lastModified", new Date(file.lastModified()));
                    fileList.add(fileInfo);
                }
            }
        }
        
        return fileList;
    }
}
